Enterprise software systems receive, generate, and store data related to many aspects of a business enterprise. In some implementations, this data may relate to marketing, sales, customer relations, supplier relations, inventory, human resources, and/or finances. Reporting systems are commonly used to present such enterprise data to users in useful formats.
Reporting systems may generate reports based on user-created report specifications. A report specification may include a layout of one or more report parts (e.g., sections, tables, charts, header, footer, etc.) and queries to specify the data that is to populate the report parts. In order to generate a report based on a report specification, a reporting system retrieves stored enterprise data based on the queries, populates the report parts with the retrieved data, and assembles the populated report parts according to the layout.
To facilitate creation of a report specification, reporting systems may interact with a semantic layer including a set of abstract entities known as business objects. Each business object associates one or more physical entities (e.g., a physical database table, associated columns of one or more database tables) of one or more enterprise data sources with user-friendly names. The user-friendly names may represent business entities, such as customers, time periods, financial figures, etc. Business objects may be classified as dimensions along which one may want to perform an analysis or report (e.g., Year, Country, Product), details (e.g., additional information on dimensions), and measures (e.g., Sales, Profit) whose values can be determined for a given combination of dimension values. A report designer may therefore create the queries of a report specification using these user-friendly names instead of references to specific physical entities of the data sources.
A semantic layer may also include a hierarchical list of values (LOV) to facilitate the selection of filter values during the creation of a query. A hierarchical LOV is associated with business objects of the semantic layer. For example, a hierarchical LOV may be associated with the Year, Quarter and Month business objects and may reflect the hierarchy Year >Quarter >Month. Each business object therefore represents a different “level” of the hierarchical LOV.
FIG. 1 illustrates the creation of a query using a hierarchical LOV according to some systems. Specifically, a user has operated query panel 100 in order to create a query that is filtered on the Month object and which returns result objects Year, Quarter, Month and Unit Sales. Hierarchical LOV 110 is presented in tree form to allow the user to intuitively select filter values from the tree. As shown, the user has selected the filter values January (of year 1997) and July (of year 1998).
As mentioned above, conventional reporting systems retrieve stored data based on such user-generated queries. For example, a conventional reporting system may receive the query created in query panel 100 and generate the following Structured Query Language (SQL) script in response:    SELECT time.year_name, time.quarter_name, time.month_name, sum(sales.unit_sales)    FROM time INNER JOIN sales on (time.time_id=sales.time_id)    WHERE time.month_name IN (‘January’, ‘July’)
The SQL script is then passed to an SQL server to retrieve a data set from a corresponding data store. FIG. 2 is a tabular representation of such a retrieved data set according to the present example. Due to the above SQL script, data set 200 includes record for January of year 1998 and July of year 1997 even though those months were not selected in hierarchical LOV 110. Conventional systems therefore fail to preserve the uniqueness of the selected filter values in the hierarchy, resulting in the retrieval of undesired data.
Systems are desired to facilitate the creation and execution of a query based on a hierarchical LOV.